package com.javachmapi.springbootjsoup.croner;

import com.javachmapi.springbootjsoup.log.PeptileTaskLog;
import lombok.extern.slf4j.Slf4j;
import net.javacrumbs.shedlock.core.SchedulerLock;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

/**
 * @desc:定时创建数据表结构，提前一天创建表
 * @to:采集数据分析
 * @author songlijun
 * @Date 2019-10-8 12:40:14
 */
@Slf4j
//@Service
public class CreateTableCronTask {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    //每晚凌晨自动创表
    @Scheduled(cron = "0 0/1 * * * ?")
    @SchedulerLock(name = "createtableJob", lockAtLeastFor = 1000 * 30)
    public void createtableJob() {
        try {
            //获取数据库表名
            String[] temp={"zby","ykz","lhs","xialv"};
            //获取得到明天的日期
            LocalDate sysdate = LocalDate.now().plusDays(1);
            String mingtian = String.valueOf(sysdate).replace("-","");

            //日志参数
            String text = "";
            String str = "";
            int state = 0;

            Connection conn = jdbcTemplate.getDataSource().getConnection();
            Statement stat = conn.createStatement();
            try{
                for(int i = 0; i <= temp.length-1; i++){
                    ResultSet rs = jdbcTemplate.getDataSource().getConnection().getMetaData().getTables(null, null, "reptile_product_"+temp[i]+"_"+mingtian, null);
                    // 判断表是否存在，如果存在则什么都不做，否则创建表
                    if( rs.next() ){
                        log.info("检测数据表reptile_product_"+temp[i]+"_"+mingtian+"已存在！");
                        continue;
                    }else{
                        // 先判断是否纯在表名，有则先删除表在创建表
                        //stat.executeUpdate("DROP TABLE IF EXISTS sys_admin_divisions;CREATE TABLE sys_admin_divisions("
                        //创建行政区划表
                        log.info("##################执行定时创表任务####################");
                        stat.executeUpdate("CREATE TABLE `reptile_product_"+temp[i]+"_"+mingtian+"`("
                                +"`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '采集序号',"
                                +"`biz` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '公众号标识',"
                                +"`info_id` int(11) NULL DEFAULT NULL COMMENT '商品编号',"
                                +"`info_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品标题',"
                                +"`img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '封面图片',"
                                +"`area_id` int(11) NULL DEFAULT NULL COMMENT '地区编号',"
                                +"`area_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '地区名称',"
                                +"`tree_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品类型',"
                                +"`order_start_date` datetime(0) NULL DEFAULT NULL COMMENT '开始抢购时间',"
                                +"`order_end_date` datetime(0) NULL DEFAULT NULL COMMENT '结束抢购时间',"
                                +"`price` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '销售价格',"
                                +"`market_price` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '市场价格',"
                                +"`order_num` int(11) NULL DEFAULT NULL COMMENT '商品销量',"
                                +"`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商家地址',"
                                +"`storage` int(11) NULL DEFAULT NULL COMMENT '剩下库存',"
                                +"`phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商家联系方式',"
                                +"`start_date` datetime(0) NULL DEFAULT NULL COMMENT '开始游玩时间',"
                                +"`end_date` datetime(0) NULL DEFAULT NULL COMMENT '结束游玩时间',"
                                +"`context` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商品描述',"
                                +"`pv` int(11) NULL DEFAULT NULL COMMENT '浏览量',"
                                +" PRIMARY KEY (`id`) USING BTREE,"
                                +" UNIQUE INDEX `info_id_index`(`info_id`) USING BTREE COMMENT '唯一索引'"
                                +") ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;"
                        );
                        state = 1;
                        str += "<reptile_product_"+temp[i]+"_"+mingtian+">";
                        log.info("创建表"+"reptile_product_"+temp[i]+"_"+mingtian+"成功！");
                    }
                }
                if(state==1){
                    text = "创建表"+ str +"成功！";
                    PeptileTaskLog.createTableLog(text, state);
                }else{
                    //表示已经存在！无需继续保存
                }
            }catch (Exception e){
                state = 0;
                text += "创建表失败,原因："+e.getMessage();
                PeptileTaskLog.createTableLog(text, state);
            }//释放资源
            stat.close();
            conn.close();
            return;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
